<?php defined('SYSPATH') or die('No direct access allowed.');

class Model_Notice extends ORM {

    public function get_notices($user_id)
    {
        $message_query = 'SELECT COUNT(*)
                          FROM `inboxes` 
                          WHERE `inboxes`.`user_id` = :user_id 
                              AND  `inboxes`.`deleted` = 0 
                              AND  `inboxes`.`folder` = 0 
                              AND  `inboxes`.`id` > (
                                SELECT `notices`.inbox_id 
                                FROM notices 
                                WHERE `notices`.`user_id` = :user_id)';
        
    //    $amount_new_messages = DB::query(DATABASE::SELECT,$message_query)
      //                             ->bind(':user_id', $user_id)
        //                           ->execute();
        
        $invitation_query = 'SELECT COUNT(*) 
                             FROM `contacts` 
                             WHERE `contacts`.`recipient_id` = :user_id 
                                AND `contacts`.`status` = 0 
                                AND `contacts`.`id` > (
                                    SELECT `notices`.contact_id 
                                    FROM notices
                                    WHERE `notices`.`user_id` = :user_id)';
        
     //   $amount_new_invitations = DB::query(DATABASE::SELECT,$invitation_query)
       //                               ->bind(':user_id', $user_id)
         //                             ->execute();
        
        $notification_query = 'SELECT COUNT(*)  
                               FROM `notifications` 
                               WHERE ((`notifications`.`resource_owner_id` = :user_id AND `notifications`.`user_id` != :user_id)
                                  )'. /*OR `notifications`.`user_id` IN (
                                    SELECT CASE `contacts`.`sender_id` 
                                           WHEN :user_id 
                                           THEN `contacts`.`recipient_id` 
                                           ELSE `contacts`.`sender_id` END AS id
                                    FROM `contacts`
                                    WHERE `contacts`.`status` = 1
                                        AND (`contacts`.`sender_id` = :user_id OR
                                             `contacts`.`recipient_id` = :user_id))) */
                                  'AND `notifications`.`id` > (
                                     SELECT `notices`.notification_id 
                                     FROM notices
                                     WHERE `notices`.`user_id` = :user_id)';
        
        $all_query = 'SELECT ('.$message_query.') AS amount_new_messages, 
                             ('.$invitation_query.') AS amount_new_invitations,
                             ('.$notification_query.') AS amount_new_notifications';
        
        $amounts_notices = DB::query(DATABASE::SELECT,$all_query)
                                      ->bind(':user_id', $user_id)
                                      ->execute();
        
        return $amounts_notices[0];
        
    }
}